package dao;

import beans.StuInfoBean;
import utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class StuInfoDaoImpl implements StuInfoDao {

    //装填学生信息
    private void getInfos(ResultSet rs, StuInfoBean s) throws SQLException {
        s.setId(rs.getString("id"));
        s.setUsername(rs.getString("username"));
        s.setGender(rs.getString("gender"));
        s.setSchool(rs.getString("school"));
        s.setTel(rs.getString("tel"));
        s.setOrigin(rs.getString("origin"));
        s.setPhoto(rs.getString("photo"));
        s.setCollege(rs.getString("college"));
        s.setMajor(rs.getString("major"));
        s.setOfClass(rs.getString("ofClass"));
    }

    //根据语句查找学生
    private List<StuInfoBean> findStudentsByColumn(String sql, String column) {
        List<StuInfoBean> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtil.getConnection();
            ps = Objects.requireNonNull(conn).prepareStatement(sql);
            ps.setObject(1, "%" + column + "%");

            rs = ps.executeQuery();
            while (rs.next()) {
                StuInfoBean s = new StuInfoBean();
                getInfos(rs, s);
                list.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(rs, ps, conn);
        }
        return list;
    }

    //向数据库中插入数据
    @Override
    public int insertStudent(StuInfoBean stu) {
        int result = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //注册驱动，获取连接
            conn = JDBCUtil.getConnection();

            //输入sql语句(向表中插入数据)
            String sql = "INSERT INTO StuInfo VALUES (?,?,?,?,?,?,?,?,?,?)";

            //预编译,传入参数
            ps = Objects.requireNonNull(conn).prepareStatement(sql);
            ps.setObject(1, stu.getId());
            ps.setObject(2, stu.getUsername());
            ps.setObject(3, stu.getGender());
            ps.setObject(4, stu.getSchool());
            ps.setObject(5, stu.getTel());
            ps.setObject(6, stu.getOrigin());
            ps.setObject(7, stu.getPhoto());
            ps.setObject(8, stu.getCollege());
            ps.setObject(9, stu.getMajor());
            ps.setObject(10, stu.getOfClass());

            //执行更新的SQL语句
            result = ps.executeUpdate();

        } catch (Exception e) {
            return result;
        } finally {
            JDBCUtil.closeAll(null, ps, conn);
        }
        return result;
    }

    //查询所有
    @Override
    public List<StuInfoBean> findAllStudents() {
        List<StuInfoBean> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtil.getConnection();
            String sql = "SELECT * FROM StuInfo";
            ps = Objects.requireNonNull(conn).prepareStatement(sql);

            rs = ps.executeQuery();
            while (rs.next()) {
                StuInfoBean s = new StuInfoBean();
                getInfos(rs, s);
                list.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(rs, ps, conn);
        }
        return list;
    }

    //根据id获取姓名
    @Override
    public String getUsernameById(String id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String username = "";

        try {
            conn = JDBCUtil.getConnection();
            String sql = "SELECT username FROM StuInfo WHERE id=?";
            ps = Objects.requireNonNull(conn).prepareStatement(sql);
            ps.setObject(1, id);
            rs = ps.executeQuery();

            while (rs.next()) {
                username = rs.getString("username");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(rs, ps, conn);
        }
        return username;
    }

    //根据id查询
    @Override
    public StuInfoBean findStudentById(String id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        StuInfoBean s = null;

        try {
            conn = JDBCUtil.getConnection();
            String sql = "SELECT * FROM StuInfo WHERE id=?";
            ps = Objects.requireNonNull(conn).prepareStatement(sql);
            ps.setObject(1, id);
            rs = ps.executeQuery();

            while (rs.next()) {
                s = new StuInfoBean();
                getInfos(rs, s);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(rs, ps, conn);
        }
        return s;
    }

    //根据名字查询
    @Override
    public List<StuInfoBean> findStudentsByName(String name) {
        String sql = "SELECT * FROM StuInfo WHERE username LIKE ?";
        return findStudentsByColumn(sql, name);
    }

    //根据班级查询
    public List<StuInfoBean> findStudentsByClass(String stuClass) {
        String sql = "SELECT * FROM StuInfo WHERE ofClass LIKE ?";
        return findStudentsByColumn(sql, stuClass);
    }

    //根据学院进行查找
    @Override
    public List<StuInfoBean> findStudentsByCollege(String college) {
        String sql = "SELECT * FROM StuInfo WHERE college LIKE ?";
        return findStudentsByColumn(sql, college);
    }

    //根据专业进行查找
    @Override
    public List<StuInfoBean> findStudentsByMajor(String major) {
        String sql = "SELECT * FROM StuInfo WHERE major LIKE ?";
        return findStudentsByColumn(sql, major);
    }

    //根据id删除
    @Override
    public int deleteStudentById(String id) {
        Connection conn = null;
        PreparedStatement ps = null;
        int result = 0;

        try {
            conn = JDBCUtil.getConnection();
            String sql = "DELETE FROM StuInfo WHERE id=?";
            ps = Objects.requireNonNull(conn).prepareStatement(sql);
            ps.setObject(1, id);
            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(null, ps, conn);
        }
        return result;
    }

    //更新表中的数据
    @Override
    public int updateStudent(StuInfoBean stu) {
        Connection conn = null;
        PreparedStatement ps = null;
        int result = 0;

        try {
            conn = JDBCUtil.getConnection();

            String sql = "UPDATE StuInfo SET username=?,gender=?,school=?,tel=?,origin=?,photo=?,college=?,major=?,ofClass=? WHERE id=?";
            ps = Objects.requireNonNull(conn).prepareStatement(sql);
            ps.setObject(1, stu.getUsername());
            ps.setObject(2, stu.getGender());
            ps.setObject(3, stu.getSchool());
            ps.setObject(4, stu.getTel());
            ps.setObject(5, stu.getOrigin());
            ps.setObject(6, stu.getPhoto());
            ps.setObject(7, stu.getCollege());
            ps.setObject(8, stu.getMajor());
            ps.setObject(9, stu.getOfClass());
            ps.setObject(10, stu.getId());
            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(null, ps, conn);
        }
        return result;
    }

}
